# 知识点管理模块功能接口
from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity, get_jwt
import exts
import datetime
import pandas as pd

knowledge_points_blueprint = Blueprint('knowledge_points', __name__)


# 上传文件实现知识点批量导入
@knowledge_points_blueprint.route('/import', methods=['POST'])
@jwt_required()
@exts.permission_required('knowledge_points', 'R')
def import_knowledge_points():
    uploaded_files = request.files.getlist('files')
    if not uploaded_files or uploaded_files[0].filename == '':
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未上传任何文件"
        }), 400
    for uploaded_file in uploaded_files:
        if not uploaded_file.filename.endswith('.xlsx'):
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": "上传的文件不是有效的 Excel 文件, 请上传后缀名为 .xlsx 的文件"
            }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for uploaded_file in uploaded_files:
            df = pd.read_excel(uploaded_file)
            df.columns = range(len(df.columns))
            df.rename(columns={
                0: 'number',
                1: 'description',
                2: 'hierarchy',
                3: 'code_id',
                4: 'subject'
            }, inplace=True)
            for index, row in df.iterrows():
                cur.execute(
                    "INSERT INTO KnowledgePoint (description, hierarchy, code_id, subject) VALUES (?, ?, ?, ?)",
                    (row['description'], row['hierarchy'], row['code_id'], row['subject'])
                )
            result.append({
                "file_name": uploaded_file.filename,
                "status": "success",
                "message": "知识点导入成功",
                "details": f"成功导入 {len(df)} 个知识点"
            })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "知识点导入成功",
            "details": result
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "知识点导入失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 实现知识点的页面手动新增操作
@knowledge_points_blueprint.route('/add', methods=['POST'])
@jwt_required()
@exts.permission_required('knowledge_points', 'C')
def add_knowledge_point():
    data = {
        "description": request.json.get('description'),
        "hierarchy": request.json.get('hierarchy'),
        "code_id": request.json.get('code_id'),
        "subject": request.json.get('subject')
    }
    for key, value in data.items():
        if not value:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"{key} 不能为空"
            }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查找code_id是否存在
        cur.execute("SELECT * FROM KnowledgePoint WHERE code_id = ?", (data['code_id'],))
        found = cur.fetchone()
        if found:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "知识点新增失败",
                "details": f"code_id {data['code_id']} 已存在"
            }), 400
        # 插入数据
        cur.execute(
            "INSERT INTO KnowledgePoint (description, hierarchy, code_id, subject) VALUES (?, ?, ?, ?)",
            (data['description'], data['hierarchy'], data['code_id'], data['subject'])
        )
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "知识点新增成功",
            "data": {
                "knowledgepoint_id": cur.lastrowid,
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "知识点新增失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 实现知识点的页面手动修改操作
@knowledge_points_blueprint.route('/update/<knowledgepoint_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('knowledge_points', 'U')
def update_knowledge_point(knowledgepoint_id):
    data = {
        "description": request.json.get('description'),
        "hierarchy": request.json.get('hierarchy'),
        "code_id": request.json.get('code_id'),
        "subject": request.json.get('subject')
    }
    for key, value in data.items():
        if not value:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"{key} 不能为空"
            }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查找知识点是否存在
        cur.execute("SELECT * FROM KnowledgePoint WHERE knowledgepoint_id = ?", (knowledgepoint_id,))
        if not cur.fetchone():
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "知识点修改失败",
                "details": f"knowledgepoint_id {knowledgepoint_id} 不存在"
            }), 400
        # 查找code_id是否存在
        cur.execute("SELECT * FROM KnowledgePoint WHERE code_id = ? AND knowledgepoint_id != ?",
                    (data['code_id'], knowledgepoint_id))
        if cur.fetchone():
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "知识点修改失败",
                "details": f"code_id {data['code_id']} 已存在"
            }), 400
        # 更新数据
        cur.execute(
            "UPDATE KnowledgePoint SET description = ?, hierarchy = ?, code_id = ?, subject = ?"
            " WHERE knowledgepoint_id = ?",
            (data['description'], data['hierarchy'], data['code_id'], data['subject'], knowledgepoint_id)
        )
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "知识点修改成功",
            "data": {
                "knowledgepoint_id": knowledgepoint_id
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "知识点修改失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 实现知识点的页面手动删除操作
@knowledge_points_blueprint.route('/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('knowledge_points', 'D')
def delete_knowledge_point():
    knowledgepoint_id = request.json.get('knowledgepoint_id')
    if not knowledgepoint_id:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "knowledgepoint_id 不能为空"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查找知识点是否存在
        cur.execute("SELECT * FROM KnowledgePoint WHERE knowledgepoint_id = ?", (knowledgepoint_id,))
        knowledge_point = cur.fetchone()
        if not knowledge_point:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "知识点删除失败",
                "details": f"knowledgepoint_id {knowledgepoint_id} 不存在"
            }), 400
        # 查找是否有子知识点
        code_id = knowledge_point['code_id']
        cur.execute("SELECT knowledgepoint_id FROM KnowledgePoint WHERE code_id LIKE ?", (f"{code_id}%",))
        children = cur.fetchall()
        if children:
            for child in children:
                cur.execute("DELETE FROM KnowledgePoint WHERE knowledgepoint_id = ?", (child['knowledgepoint_id'],))
        # 删除知识点
        cur.execute("DELETE FROM KnowledgePoint WHERE knowledgepoint_id = ?", (knowledgepoint_id,))
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "知识点删除成功",
            "data": {
                "knowledgepoint_id": knowledgepoint_id,
                "children": len(children)
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "知识点删除失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 知识点分页查询接口
@knowledge_points_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('knowledge_points', 'R')
def query_knowledge_points():
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)
    subject = request.args.get('subject')
    hierarchy = request.args.get('hierarchy')
    description = request.args.get('description')
    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须大于 0"
        }), 400

    offset = (page - 1) * page_size

    # 构建查询条件
    conditions = []
    if subject:
        conditions.append(f"subject = '{subject}'")
    if hierarchy:
        conditions.append(f"hierarchy = '{hierarchy}'")
    if description:
        conditions.append(f"description LIKE '%{description}%'")
    where = " AND ".join(conditions) if conditions else "1=1"

    sql = f"""
    SELECT knowledgepoint_id, description, hierarchy, code_id, subject
    FROM KnowledgePoint
    WHERE {where}
    ORDER BY knowledgepoint_id DESC
    LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
    SELECT COUNT(*) AS total
    FROM KnowledgePoint
    WHERE {where}
    """

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查询总记录数
        cur.execute(sql_count)
        total_records = cur.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询数据
        cur.execute(sql)
        results = cur.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的试题信息"
            }), 404
        results = [dict(zip([column[0] for column in cur.description], row)) for row in results]

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "查询失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 获取所有知识点构建树形结构
@knowledge_points_blueprint.route('/tree', methods=['GET'])
@jwt_required()
@exts.permission_required('knowledge_points', 'R')
def get_knowledge_point_tree():
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.execute("SELECT * FROM KnowledgePoint")
        knowledge_points = cur.fetchall()
        if not knowledge_points:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到知识点信息"
            }), 404
        knowledge_points = [dict(knowledge_point) for knowledge_point in knowledge_points]
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": knowledge_points
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "查询失败",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()
